package df.bean.process;

import df.bean.db.conn.DBConnection;
import df.bean.db.table.Batch;
import df.bean.db.table.TRN_Error;
import df.bean.db.table.TrnDaily;
import df.bean.obj.util.JDate;


public class ProcessImport {
    private DBConnection conn = null;
    private String user_id = "";
    
    public void setUserId(String s){
    	user_id = s;
    }
    public ProcessImport(DBConnection conn) {
        this.conn = conn;
    }
    // TrnDaily
    public boolean importBill(String hospitalCode, String startDate, String endDate) {
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",FIRST_DEPARTMENT_CODE "
           + ",FIRST_LOCATION_CODE "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",PRIVATE_DOCTOR_CODE "//NEW
           + ",VERIFY_DATE "
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR "
           + ",OLD_DR_AMT "
           + ",ORDER_ITEM_CATEGORY_CODE )"
     + " SELECT HOSPITAL_CODE" + ",BILL_NO" + ", BILL_DATE" + ", '', ''"
           + ", TRANSACTION_DATE" + ", HN_NO" + ", PATIENT_NAME" + ", EPISODE_NO" 
           + ", PAYOR_CODE" + ", PAYOR_NAME" + ", '" + TrnDaily.TRANSACTION_MODULE_TR + "'"
           + ", TRANSACTION_TYPE"
           + ", PAYOR_CATEGORY_CODE" + ", PAYOR_CATEGORY_DESC, 'N' "
           + ", LINE_NO" 
           + ", ADMISSION_TYPE_CODE"
           + ", NATIONALITY_CODE, NATIONALITY_DESCRIPTION"
           + ", FIRST_LOCATION_DEPT_CODE" + ", FIRST_LOCATION_CODE"
           + ", PATIENT_LOCATION_DEPT_CODE" + ", PATIENT_LOCATION_CODE"
           + ", RECEIVING_LOCATION_DEPT_CODE" + ", RECEIVING_LOCATION_CODE" 
           + ", '', ORDER_ITEM_CODE" 
           + ", ORDER_ITEM_DESCRIPTION" + ", DOCTOR_CODE" + ", DOCTOR_PRIVATE" + ", VERIFIED_DATE" 
           + ", VERIFIED_TIME"  + ", DOCTOR_CODE" 
           + ", ORDERED_DATE, ORDERED_TIME, '', DOCTOR_CODE" 
           + ", RECEIPT_TYPE_CODE" + ", AMOUNT_BEF_DISCOUNT" 
           + ", AMOUNT_OF_DISCOUNT" + ", AMOUNT_BEF_DISCOUNT-AMOUNT_OF_DISCOUNT, AMOUNT_BEF_DISCOUNT" 
           + ", 1, 1, " 
           + "'" + JDate.getDate() +"','" + JDate.getTime() + "'" + ",'" + this.conn.getUserID() + "'"
           + ",'', '','','',0,0,0,0,0,0,0,'',0,0,'','','','','','','',0,0,0,'','','','','',0,0,'1',INVOICE_TYPE " 
           + ",BILL_TOTAL_AMOUNT,0,AMOUNT_BEF_DISCOUNT-AMOUNT_OF_DISCOUNT"
           + ",'N','N','N','N','N',0, ''"
           + " FROM INT_HIS_BILL " +
             " WHERE (TRANSACTION_DATE >= '" + startDate + "'" +
             " AND HOSPITAL_CODE = '" + hospitalCode + "'" +
             " AND AMOUNT_BEF_DISCOUNT <> 0"+
             " AND TRANSACTION_DATE <= '" + endDate + "')";
           
        try {
//          conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
            System.out.println("Import Bill : " + sql);
            TRN_Error.setUser_name(this.conn.getUserID());
            TRN_Error.writeErrorLog(this.conn.getConnection(), TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import data error", ex.getMessage(), sql);
            
        } finally {
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }
    // TrnPatho
    public boolean importPatho(String hospitalCode, String startDate, String endDate) {
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",VERIFY_DATE " //LINE 29
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR "
           + ",OLD_DR_AMT "
           + ",ORDER_ITEM_CATEGORY_CODE )"
     + " SELECT HOSPITAL_CODE" + ",BILL_NO" + ", BILL_DATE" + ", '', ''"
           + ", TRANSACTION_DATE" + ", HN_NO" + ", PATIENT_NAME" + ", EPISODE_NO" 
           + ", PAYOR_CODE" + ", PAYOR_NAME" + ", '" + TrnDaily.TRANSACTION_MODULE_TR + "'"
           + ", TRANSACTION_TYPE"
           + ", PAYOR_CATEGORY_CODE" + ", PAYOR_CATEGORY_DESC, 'N' "
           + ", LINE_NO" 
           + ", ADMISSION_TYPE_CODE"
           + ", NATIONALITY_CODE, NATIONALITY_DESCRIPTION"
           + ", PATIENT_LOCATION_DEPT_CODE" + ", PATIENT_LOCATION_CODE"
           + ", RECEIVING_LOCATION_DEPT_CODE" + ", RECEIVING_LOCATION_CODE" 
           + ", '', ORDER_ITEM_CODE" 
           + ", ORDER_ITEM_DESCRIPTION" + ", DOCTOR_CODE" + ", VERIFIED_DATE" 
           + ", VERIFIED_TIME"  + ", DOCTOR_CODE" 
           + ", ORDERED_DATE, ORDERED_TIME, '', DOCTOR_CODE" 
           + ", RECEIPT_TYPE_CODE" + ", AMOUNT_BEF_DISCOUNT" 
           + ", AMOUNT_OF_DISCOUNT" + ", AMOUNT_BEF_DISCOUNT-AMOUNT_OF_DISCOUNT, AMOUNT_BEF_DISCOUNT" 
           + ", 1, 1, " 
           + "'" + JDate.getDate() +"','" + JDate.getTime() + "'" + ",'" + this.conn.getUserID() + "'"
           + ",'', '','','',0,0,0,0,0,0,0,'',0,0,'','','','','','','',0,0,0,'','','','','',0,0,'1',INVOICE_TYPE " 
           + ",BILL_TOTAL_AMOUNT,0,AMOUNT_BEF_DISCOUNT-AMOUNT_OF_DISCOUNT"
           + ",'N','N','N','N','N',0, ''"
           + " FROM INT_HIS_BILL " +
             " WHERE (TRANSACTION_DATE >= '" + startDate + "'" +
             " AND HOSPITAL_CODE = '" + hospitalCode + "'" +
             " AND AMOUNT_BEF_DISCOUNT <> 0 AND RECEIVING_LOCATION_CODE = '01PAT'"+
             " AND TRANSACTION_DATE <= '" + endDate + "')";
           
        try {
//            conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
//            DialogBox.ShowError("Error : " + ex.getMessage());
            System.out.println("Import Bill : " + sql);
            TRN_Error.writeErrorLog(this.conn.getConnection(), TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import data error", ex.getMessage(), sql);
            
        } finally {
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }
    // Verify
    public boolean importVerifyInMonth(String hospitalCode, String startDate, String endDate) {
    	Batch batch = new Batch(hospitalCode, conn);
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",FIRST_DEPARTMENT_CODE "
           + ",FIRST_LOCATION_CODE "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",PRIVATE_DOCTOR_CODE "
           + ",VERIFY_DATE "
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR " 
           + ",OLD_DR_AMT "
           + ",ORDER_ITEM_CATEGORY_CODE )"
     + " SELECT T.HOSPITAL_CODE "
           + ",T.INVOICE_NO "
           + ",T.INVOICE_DATE "
           + ",T.RECEIPT_NO "
           + ",T.RECEIPT_DATE "
           + ",V.TRANSACTION_DATE "
           + ",T.HN_NO "
           + ",T.PATIENT_NAME "
           + ",T.EPISODE_NO "
           + ",T.PAYOR_OFFICE_CODE "
           + ",T.PAYOR_OFFICE_NAME "
           + ",T.TRANSACTION_MODULE "
           + ",T.TRANSACTION_TYPE "
           + ",T.PAYOR_OFFICE_CATEGORY_CODE "
           + ",T.PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",T.IS_WRITE_OFF "
           + ",T.LINE_NO+'V' "
           + ",T.ADMISSION_TYPE_CODE "
           + ",T.NATIONALITY_CODE "
           + ",T.NATIONALITY_DESCRIPTION "
           + ",T.FIRST_DEPARTMENT_CODE "
           + ",T.FIRST_LOCATION_CODE "
           + ",T.PATIENT_DEPARTMENT_CODE "
           + ",T.PATIENT_LOCATION_CODE "
           + ",T.RECEIPT_DEPARTMENT_CODE "
           + ",T.RECEIPT_LOCATION_CODE "
           + ",D.DEPARTMENT_CODE "   // DOCTOR_DEPARTMENT_CODE ดูว่าใช้ department อะไร
           + ",T.ORDER_ITEM_CODE "
           + ",T.ORDER_ITEM_DESCRIPTION "
           + ",V.DOCTOR_CODE "
           + ",T.PRIVATE_DOCTOR_CODE "
           + ",V.VERIFIED_DATE "
           + ",V.VERIFIED_TIME "
           + ",T.DOCTOR_EXECUTE_CODE "
           + ",T.EXECUTE_DATE "
           + ",T.EXECUTE_TIME "
           + ",V.DOCTOR_CODE "
           + ",V.DOCTOR_CODE "
           + ",T.RECEIPT_TYPE_CODE "
           + ",T.AMOUNT_BEF_DISCOUNT "
           + ",T.AMOUNT_OF_DISCOUNT "
           + ",T.AMOUNT_AFT_DISCOUNT "
           + ",T.AMOUNT_BEF_WRITE_OFF "
           + ",T.INV_IS_VOID "
           + ",T.REC_IS_VOID "
           + ",'" + JDate.getDate() + "'"
           + ",'" + JDate.getTime() + "'"
           + ",'" + this.conn.getUserID() + "'"
           + ",'' " // Batch No
           + ",T.YYYY "
           + ",T.MM "
           + ",T.DD "
           + ",0" //T.NOR_ALLOCATE_AMT 
           + ",0" //T.NOR_ALLOCATE_PCT "
           + ",0" //T.DR_AMT "
           + ",0" //T.DR_TAX_400 "
           + ",0" //T.DR_TAX_401 "
           + ",0" //T.DR_TAX_402 "
           + ",0" //T.DR_TAX_406 "
           + ",''" //T.TAX_TYPE_CODE "
           + ",0" //T.DR_PREMIUM "
           + ",0" //T.GUARANTEE_AMT "
           + ",''" //T.GUARANTEE_CODE "
           + ",''" //T.GUARANTEE_DR_CODE "
           + ",''" //T.GUARANTEE_TYPE "
           + ",''" //T.GUARANTEE_DATE_TIME "
           + ",''" //T.GUARANTEE_TERM_MM "
           + ",''" //T.GUARANTEE_TERM_YYYY "
           + ",''" //T.IS_GUARANTEE "
           + ",0" //T.HP_AMT "
           + ",0" //T.HP_PREMIUM "
           + ",0" //T.HP_TAX "
           + ",''" //T.COMPUTE_DAILY_DATE "
           + ",''" //T.COMPUTE_DAILY_TIME "
           + ",''" //T.COMPUTE_DAILY_USER_ID "
           + ",''" //T.DOCTOR_CATEGORY_CODE "
           + ",''" //T.EXCLUDE_TREATMENT "
           + ",0" //T.PREMIUM_CHARGE_PCT "
           + ",0" //T.PREMIUM_REC_AMT "
           + ",T.ACTIVE "
           + ",'" + TrnDaily.INVOICE_TYPE_RESULT + "'"  // T.INVOICE_TYPE
           + ",T.TOTAL_BILL_AMOUNT "
           + ",T.TOTAL_DR_REC_AMOUNT "
           + ",T.OLD_AMOUNT "
           + ",T.PAY_BY_CASH "
           + ",T.PAY_BY_AR "
           + ",T.PAY_BY_DOCTOR "
           + ",T.PAY_BY_PAYOR "
           + ",T.PAY_BY_CASH_AR "
           + ",0 "  // T.OLD_DR_AMT
           + ",T.ORDER_ITEM_CATEGORY_CODE "
           + " FROM INT_HIS_VERIFY V INNER JOIN TRN_DAILY T ON V.HOSPITAL_CODE = T.HOSPITAL_CODE " 
           //+ " AND V.LINE_NO = T.LINE_NO"
           + " AND V.BILL_NO = T.INVOICE_NO AND V.LINE_NO = T.LINE_NO"
           + " LEFT JOIN DOCTOR D ON V.DOCTOR_CODE = D.CODE AND V.HOSPITAL_CODE = D.HOSPITAL_CODE "
           + " WHERE (V.TRANSACTION_DATE >= '" + startDate + "'"
           + " AND V.TRANSACTION_DATE <= '" + endDate + "')"
           + " AND T.INVOICE_TYPE = '" + TrnDaily.INVOICE_TYPE_ORDER + "'"
           + " AND V.HOSPITAL_CODE = '" + hospitalCode + "'" 
           + " AND T.INVOICE_DATE >= '" + batch.getYyyy() + batch.getMm() + "01'"
           + " AND T.INVOICE_DATE <= '" + batch.getYyyy() + batch.getMm() + "31'";           
        try {
//            conn.beginTrans();
        	System.out.println("SQL From Verify In Monthl : "+sql);
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
//            DialogBox.ShowError("Error : " + ex.getMessage());
            System.out.println("importVerifyInMonth" + ex.getMessage());
            TRN_Error.writeErrorLog(this.conn.getConnection(), TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import verify data is fails", ex.getMessage(), sql);
        } finally {
        	batch=null;
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }    
    // Verify
    public boolean importVerifyRecOverMonth(String hospitalCode, String startDate, String endDate) {
    	Batch batch = new Batch(hospitalCode, conn);
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",FIRST_DEPARTMENT_CODE "
           + ",FIRST_LOCATION_CODE "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",PRIVATE_DOCTOR_CODE "
           + ",VERIFY_DATE "
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR "
           + ",OLD_DR_AMT "
           + ",ORDER_ITEM_CATEGORY_CODE )"
     + " SELECT T.HOSPITAL_CODE "
           + ",T.INVOICE_NO "
           + ",V.TRANSACTION_DATE " // T.INVOICE_DATE // DIFF
           + ",T.RECEIPT_NO "
           + ",T.RECEIPT_DATE "
           + ",V.TRANSACTION_DATE "
           + ",T.HN_NO "
           + ",T.PATIENT_NAME "
           + ",T.EPISODE_NO "
           + ",T.PAYOR_OFFICE_CODE "
           + ",T.PAYOR_OFFICE_NAME "
           + ",T.TRANSACTION_MODULE "
           + ",T.TRANSACTION_TYPE "
           + ",T.PAYOR_OFFICE_CATEGORY_CODE "
           + ",T.PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",T.IS_WRITE_OFF "
           + ",T.LINE_NO+'V' "
           + ",T.ADMISSION_TYPE_CODE "
           + ",T.NATIONALITY_CODE "
           + ",T.NATIONALITY_DESCRIPTION "
           + ",T.FIRST_DEPARTMENT_CODE "
           + ",T.FIRST_LOCATION_CODE "
           + ",T.PATIENT_DEPARTMENT_CODE "
           + ",T.PATIENT_LOCATION_CODE "
           + ",T.RECEIPT_DEPARTMENT_CODE "
           + ",T.RECEIPT_LOCATION_CODE "
           + ",D.DEPARTMENT_CODE "   // DOCTOR_DEPARTMENT_CODE ดูว่าใช้ department อะไร
           + ",T.ORDER_ITEM_CODE "
           + ",T.ORDER_ITEM_DESCRIPTION "
           + ",V.DOCTOR_CODE "
           + ",T.PRIVATE_DOCTOR_CODE "
           + ",V.VERIFIED_DATE "
           + ",V.VERIFIED_TIME "
           + ",T.DOCTOR_EXECUTE_CODE "
           + ",T.EXECUTE_DATE "
           + ",T.EXECUTE_TIME "
           + ",V.DOCTOR_CODE "
           + ",V.DOCTOR_CODE "
           + ",T.RECEIPT_TYPE_CODE "
           + ",T.AMOUNT_BEF_DISCOUNT "
           + ",T.AMOUNT_OF_DISCOUNT "
           + ",T.AMOUNT_AFT_DISCOUNT "
           + ",T.AMOUNT_BEF_WRITE_OFF "
           + ",T.INV_IS_VOID "
           + ",T.REC_IS_VOID "
           + ",'" + JDate.getDate() + "'"
           + ",'" + JDate.getTime() + "'"
           + ",'" + this.conn.getUserID() + "'"
           + ",'' " // Batch No
           + ",'" + batch.getYyyy() + "'" // T.YYYY // diff
           + ",'" + batch.getMm() + "'" //T.MM // diff
           + ",T.DD "   // not sure
           + ",0" //T.NOR_ALLOCATE_AMT 
           + ",0" //T.NOR_ALLOCATE_PCT "
           + ",0" //T.DR_AMT "
           + ",0" //T.DR_TAX_400 "
           + ",0" //T.DR_TAX_401 "
           + ",0" //T.DR_TAX_402 "
           + ",0" //T.DR_TAX_406 "
           + ",''" //T.TAX_TYPE_CODE "
           + ",0" //T.DR_PREMIUM "
           + ",0" //T.GUARANTEE_AMT "
           + ",''" //T.GUARANTEE_CODE "
           + ",''" //T.GUARANTEE_DR_CODE "
           + ",''" //T.GUARANTEE_TYPE "
           + ",''" //T.GUARANTEE_DATE_TIME "
           + ",''" //T.GUARANTEE_TERM_MM "
           + ",''" //T.GUARANTEE_TERM_YYYY "
           + ",''" //T.IS_GUARANTEE "
           + ",0" //T.HP_AMT "
           + ",0" //T.HP_PREMIUM "
           + ",0" //T.HP_TAX "
           + ",''" //T.COMPUTE_DAILY_DATE "
           + ",''" //T.COMPUTE_DAILY_TIME "
           + ",''" //T.COMPUTE_DAILY_USER_ID "
           + ",''" //T.DOCTOR_CATEGORY_CODE "
           + ",''" //T.EXCLUDE_TREATMENT "
           + ",0" //T.PREMIUM_CHARGE_PCT "
           + ",0" //T.PREMIUM_REC_AMT "
           + ",T.ACTIVE "
           + ",'" + TrnDaily.INVOICE_TYPE_RESULT + "'"  // T.INVOICE_TYPE
           + ",T.TOTAL_BILL_AMOUNT "
           + ",T.TOTAL_DR_REC_AMOUNT "
           + ",T.OLD_AMOUNT "
           + ",T.PAY_BY_CASH "
           + ",T.PAY_BY_AR "
           + ",T.PAY_BY_DOCTOR "
           + ",T.PAY_BY_PAYOR "
           + ",T.PAY_BY_CASH_AR "
           + ",0 " //T.OLD_DR_AMT
           + ",T.ORDER_ITEM_CATEGORY_CODE "
           + " FROM INT_HIS_VERIFY V INNER JOIN TRN_DAILY T ON V.HOSPITAL_CODE = T.HOSPITAL_CODE " 
           //+ " AND V.LINE_NO = T.LINE_NO"
           + " AND V.BILL_NO = T.INVOICE_NO AND V.LINE_NO = T.LINE_NO"
           + " LEFT JOIN DOCTOR D ON V.DOCTOR_CODE = D.CODE AND V.HOSPITAL_CODE = D.HOSPITAL_CODE "
           + " WHERE (V.TRANSACTION_DATE >= '" + startDate + "'"
           + " AND V.TRANSACTION_DATE <= '" + endDate + "')"
           + " AND T.INVOICE_TYPE = '" + TrnDaily.INVOICE_TYPE_ORDER + "'"
           + " AND V.HOSPITAL_CODE = '" + hospitalCode + "'" 
           + " AND T.INVOICE_DATE < '" + batch.getYyyy() + batch.getMm() + "00'" // difference
           + " AND (T.PAY_BY_CASH = 'Y' OR T.PAY_BY_AR = 'Y' OR T.PAY_BY_DOCTOR = 'Y' "
           + " OR T.PAY_BY_PAYOR = 'Y' OR T.PAY_BY_CASH_AR = 'Y')";
           
        try {
//            conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
            System.out.println("importVerifyRecOverMonth" + ex.getMessage());
            TRN_Error.writeErrorLog(this.conn.getConnection(), TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import verify data is fails", ex.getMessage(), sql);
            //            DialogBox.ShowError("Error : " + ex.getMessage());
        } finally {
        	batch = null;
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }    
    
    // Verify
    public boolean importVerifyNotRec(String hospitalCode, String startDate, String endDate) {
    	Batch batch = new Batch(hospitalCode, conn);
        boolean result = true;
        String sql = "INSERT INTO TRN_DAILY (" 
           + "HOSPITAL_CODE "
           + ",INVOICE_NO "
           + ",INVOICE_DATE "
           + ",RECEIPT_NO "
           + ",RECEIPT_DATE "
           + ",TRANSACTION_DATE "
           + ",HN_NO "
           + ",PATIENT_NAME "
           + ",EPISODE_NO "
           + ",PAYOR_OFFICE_CODE "
           + ",PAYOR_OFFICE_NAME "
           + ",TRANSACTION_MODULE "
           + ",TRANSACTION_TYPE "
           + ",PAYOR_OFFICE_CATEGORY_CODE "
           + ",PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",IS_WRITE_OFF "
           + ",LINE_NO "
           + ",ADMISSION_TYPE_CODE "
           + ",NATIONALITY_CODE "
           + ",NATIONALITY_DESCRIPTION "
           + ",PATIENT_DEPARTMENT_CODE "
           + ",PATIENT_LOCATION_CODE "
           + ",RECEIPT_DEPARTMENT_CODE "
           + ",RECEIPT_LOCATION_CODE "
           + ",DOCTOR_DEPARTMENT_CODE "
           + ",ORDER_ITEM_CODE "
           + ",ORDER_ITEM_DESCRIPTION "
           + ",DOCTOR_CODE "
           + ",VERIFY_DATE "
           + ",VERIFY_TIME "
           + ",DOCTOR_EXECUTE_CODE "
           + ",EXECUTE_DATE "
           + ",EXECUTE_TIME "
           + ",DOCTOR_RESULT_CODE "
           + ",OLD_DOCTOR_CODE "
           + ",RECEIPT_TYPE_CODE "
           + ",AMOUNT_BEF_DISCOUNT "
           + ",AMOUNT_OF_DISCOUNT "
           + ",AMOUNT_AFT_DISCOUNT "
           + ",AMOUNT_BEF_WRITE_OFF "
           + ",INV_IS_VOID "
           + ",REC_IS_VOID "
           + ",UPDATE_DATE "
           + ",UPDATE_TIME "
           + ",USER_ID "
           + ",BATCH_NO "
           + ",YYYY "
           + ",MM "
           + ",DD "
           + ",NOR_ALLOCATE_AMT "
           + ",NOR_ALLOCATE_PCT "
           + ",DR_AMT "
           + ",DR_TAX_400 "
           + ",DR_TAX_401 "
           + ",DR_TAX_402 "
           + ",DR_TAX_406 "
           + ",TAX_TYPE_CODE "
           + ",DR_PREMIUM "
           + ",GUARANTEE_AMT "
           + ",GUARANTEE_CODE "
           + ",GUARANTEE_DR_CODE "
           + ",GUARANTEE_TYPE "
           + ",GUARANTEE_DATE_TIME "
           + ",GUARANTEE_TERM_MM "
           + ",GUARANTEE_TERM_YYYY "
           + ",IS_GUARANTEE "
           + ",HP_AMT "
           + ",HP_PREMIUM "
           + ",HP_TAX "
           + ",COMPUTE_DAILY_DATE "
           + ",COMPUTE_DAILY_TIME "
           + ",COMPUTE_DAILY_USER_ID "
           + ",DOCTOR_CATEGORY_CODE "
           + ",EXCLUDE_TREATMENT "
           + ",PREMIUM_CHARGE_PCT "
           + ",PREMIUM_REC_AMT "
           + ",ACTIVE "
           + ",INVOICE_TYPE "
           + ",TOTAL_BILL_AMOUNT "
           + ",TOTAL_DR_REC_AMOUNT "
           + ",OLD_AMOUNT "
           + ",PAY_BY_CASH "
           + ",PAY_BY_AR "
           + ",PAY_BY_DOCTOR "
           + ",PAY_BY_PAYOR "
           + ",PAY_BY_CASH_AR "
           + ",OLD_DR_AMT) "
     + " SELECT T.HOSPITAL_CODE "
           + ",T.INVOICE_NO "
           + ",V.TRANSACTION_DATE " // T.INVOICE_DATE // DIFF
           + ",T.RECEIPT_NO "
           + ",T.RECEIPT_DATE "
           + ",V.TRANSACTION_DATE "
           + ",T.HN_NO "
           + ",T.PATIENT_NAME "
           + ",T.EPISODE_NO "
           + ",T.PAYOR_OFFICE_CODE "
           + ",T.PAYOR_OFFICE_NAME "
           + ",T.TRANSACTION_MODULE "
           + ",T.TRANSACTION_TYPE "
           + ",T.PAYOR_OFFICE_CATEGORY_CODE "
           + ",T.PAYOR_OFFICE_CATEGORY_DESCRIPTION "
           + ",T.IS_WRITE_OFF "
           + ",T.LINE_NO+'V' "
           + ",T.ADMISSION_TYPE_CODE "
           + ",T.NATIONALITY_CODE "
           + ",T.NATIONALITY_DESCRIPTION "
           + ",T.PATIENT_DEPARTMENT_CODE "
           + ",T.PATIENT_LOCATION_CODE "
           + ",T.RECEIPT_DEPARTMENT_CODE "
           + ",T.RECEIPT_LOCATION_CODE "
           + ",D.DEPARTMENT_CODE "   // DOCTOR_DEPARTMENT_CODE ดูว่าใช้ department อะไร
           + ",T.ORDER_ITEM_CODE "
           + ",T.ORDER_ITEM_DESCRIPTION "
           + ",V.DOCTOR_CODE "
           + ",V.VERIFIED_DATE "
           + ",V.VERIFIED_TIME "
           + ",T.DOCTOR_EXECUTE_CODE "
           + ",T.EXECUTE_DATE "
           + ",T.EXECUTE_TIME "
           + ",V.DOCTOR_CODE "
           + ",V.DOCTOR_CODE "
           + ",T.RECEIPT_TYPE_CODE "
           + ",T.AMOUNT_BEF_DISCOUNT "
           + ",T.AMOUNT_OF_DISCOUNT "
           + ",T.AMOUNT_AFT_DISCOUNT "
           + ",T.AMOUNT_BEF_WRITE_OFF "
           + ",T.INV_IS_VOID "
           + ",T.REC_IS_VOID "
           + ",'" + JDate.getDate() + "'"
           + ",'" + JDate.getTime() + "'"
           + ",'" + this.conn.getUserID() + "'"
           + ",'' " // Batch No
           + ", T.YYYY "
           + ", T.MM "
           + ",T.DD "
           + ",0" //T.NOR_ALLOCATE_AMT 
           + ",0" //T.NOR_ALLOCATE_PCT "
           + ",0" //T.DR_AMT "
           + ",0" //T.DR_TAX_400 "
           + ",0" //T.DR_TAX_401 "
           + ",0" //T.DR_TAX_402 "
           + ",0" //T.DR_TAX_406 "
           + ",''" //T.TAX_TYPE_CODE "
           + ",0" //T.DR_PREMIUM "
           + ",0" //T.GUARANTEE_AMT "
           + ",''" //T.GUARANTEE_CODE "
           + ",''" //T.GUARANTEE_DR_CODE "
           + ",''" //T.GUARANTEE_TYPE "
           + ",''" //T.GUARANTEE_DATE_TIME "
           + ",''" //T.GUARANTEE_TERM_MM "
           + ",''" //T.GUARANTEE_TERM_YYYY "
           + ",''" //T.IS_GUARANTEE "
           + ",0" //T.HP_AMT "
           + ",0" //T.HP_PREMIUM "
           + ",0" //T.HP_TAX "
           + ",''" //T.COMPUTE_DAILY_DATE "
           + ",''" //T.COMPUTE_DAILY_TIME "
           + ",''" //T.COMPUTE_DAILY_USER_ID "
           + ",''" //T.DOCTOR_CATEGORY_CODE "
           + ",''" //T.EXCLUDE_TREATMENT "
           + ",0" //T.PREMIUM_CHARGE_PCT "
           + ",0" //T.PREMIUM_REC_AMT "
           + ",T.ACTIVE "
           + ",'" + TrnDaily.INVOICE_TYPE_RESULT + "'"  // T.INVOICE_TYPE
           + ",T.TOTAL_BILL_AMOUNT "
           + ",T.TOTAL_DR_REC_AMOUNT "
           + ",T.OLD_AMOUNT "
           + ",T.PAY_BY_CASH "
           + ",T.PAY_BY_AR "
           + ",T.PAY_BY_DOCTOR "
           + ",T.PAY_BY_PAYOR "
           + ",T.PAY_BY_CASH_AR"
           + ",0 "  // T.OLD_DR_AMT
           + " FROM INT_HIS_VERIFY V INNER JOIN TRN_DAILY T ON V.HOSPITAL_CODE = T.HOSPITAL_CODE " 
           //+ " AND V.LINE_NO = T.LINE_NO"
           + " AND V.BILL_NO = T.INVOICE_NO AND V.LINE_NO = T.LINE_NO"
           + " LEFT JOIN DOCTOR D ON V.DOCTOR_CODE = D.CODE AND V.HOSPITAL_CODE = D.HOSPITAL_CODE "
           + " WHERE V.TRANSACTION_DATE >= '" + startDate + "'"
           + " AND V.TRANSACTION_DATE <= '" + endDate + "'"
           + " AND T.INVOICE_TYPE = '" + TrnDaily.INVOICE_TYPE_ORDER + "'"
           + " AND V.HOSPITAL_CODE = '" + hospitalCode + "'" 
           + " AND T.INVOICE_DATE < '" + batch.getYyyy() + batch.getMm() + "00'" // difference
           + " AND (T.PAY_BY_CASH = 'N' AND T.PAY_BY_AR = 'N' AND T.PAY_BY_DOCTOR = 'N' "
           + " AND T.PAY_BY_PAYOR = 'N' AND T.PAY_BY_CASH_AR = 'N')";
        
        try {
//            conn.beginTrans();
            if (conn.executeUpdate(sql) >= 0) { result = true; } else { result = false; }
        } catch (Exception ex) {
//            DialogBox.ShowError("Error : " + ex.getMessage());
            System.out.println("importVerifyNotRec" + ex.getMessage());
            TRN_Error.writeErrorLog(this.conn.getConnection(), TRN_Error.PROCESS_IMPORT_BILL, 
                    "Import verify data is fail.", ex.getMessage(), sql);
        } finally {
        	batch=null;
//            if (result) { conn.commitTrans(); }
//            if (!result) { conn.rollBackTrans(); }
        }
        
        return result;    
    }    
}
